﻿using System.Data.SQLite;

namespace StudentManagementSystem.DB
{
    public class DBContext
    {
        private readonly string _connectionString;

        public DBContext(string databasePath)
        {
            _connectionString = $"Data Source={databasePath};Version=3;";
            InitializeDatabase();
        }

        public SQLiteConnection GetConnection()
        {
            return new SQLiteConnection(_connectionString);
        }

        private void InitializeDatabase()
        {
            using (var connection = GetConnection())
            {
                connection.Open();

                // 创建学校表
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        CREATE TABLE IF NOT EXISTS Schools (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT NOT NULL,
                            Address TEXT,
                            CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                        )";
                    command.ExecuteNonQuery();
                }

                // 创建班级表
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        CREATE TABLE IF NOT EXISTS Classes (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT NOT NULL,
                            SchoolId INTEGER NOT NULL,
                            CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
                            FOREIGN KEY (SchoolId) REFERENCES Schools(Id)
                        )";
                    command.ExecuteNonQuery();
                }

                // 创建学生表
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        CREATE TABLE IF NOT EXISTS Students (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT NOT NULL,
                            Age INTEGER,
                            Gender TEXT,
                            ClassId INTEGER NOT NULL,
                            CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
                            FOREIGN KEY (ClassId) REFERENCES Classes(Id)
                        )";
                    command.ExecuteNonQuery();
                }

                // 创建日志表
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        CREATE TABLE IF NOT EXISTS Logs (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Action TEXT NOT NULL,
                            TableName TEXT NOT NULL,
                            RecordId TEXT,
                            Details TEXT,
                            CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                        )";
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}